﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Repositories
{
    public class LogRepository : BaseRepository
    {
        public LogRepository(DB.DBContext dbContext) : base(dbContext) { }

        public int Create(Log log)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = @"
                        INSERT INTO Logs (Action, TableName, RecordId, Details, CreatedAt)
                        VALUES (@Action, @TableName, @RecordId, @Details, @CreatedAt);
                        SELECT last_insert_rowid();";
                    command.Parameters.AddWithValue("@Action", log.Action);
                    command.Parameters.AddWithValue("@TableName", log.TableName);
                    command.Parameters.AddWithValue("@RecordId", log.RecordId != null ? (object)log.RecordId : DBNull.Value);
                    command.Parameters.AddWithValue("@Details", log.Details != null ? (object)log.Details : DBNull.Value);
                    command.Parameters.AddWithValue("@CreatedAt", log.CreatedAt);
                    return Convert.ToInt32(command.ExecuteScalar());
                }
            }
        }

        public Log GetById(int id)
        {
            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Logs WHERE Id = @Id";
                    command.Parameters.AddWithValue("@Id", id);

                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            return new Log
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Action = reader.GetString(reader.GetOrdinal("Action")),
                                TableName = reader.GetString(reader.GetOrdinal("TableName")),
                                RecordId = reader.IsDBNull(reader.GetOrdinal("RecordId")) ? null : reader.GetString(reader.GetOrdinal("RecordId")),
                                Details = reader.IsDBNull(reader.GetOrdinal("Details")) ? null : reader.GetString(reader.GetOrdinal("Details")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
                            };
                        }
                    }
                }
            }

            return null;
        }

        public List<Log> GetAll()
        {
            var logs = new List<Log>();

            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Logs ORDER BY CreatedAt DESC";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            logs.Add(new Log
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Action = reader.GetString(reader.GetOrdinal("Action")),
                                TableName = reader.GetString(reader.GetOrdinal("TableName")),
                                RecordId = reader.IsDBNull(reader.GetOrdinal("RecordId")) ? null : reader.GetString(reader.GetOrdinal("RecordId")),
                                Details = reader.IsDBNull(reader.GetOrdinal("Details")) ? null : reader.GetString(reader.GetOrdinal("Details")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
                            });
                        }
                    }
                }
            }

            return logs;
        }

        public List<Log> GetByTableName(string tableName)
        {
            var logs = new List<Log>();

            using (var connection = _dbContext.GetConnection())
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "SELECT * FROM Logs WHERE TableName = @TableName ORDER BY CreatedAt DESC";
                    command.Parameters.AddWithValue("@TableName", tableName);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            logs.Add(new Log
                            {
                                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                                Action = reader.GetString(reader.GetOrdinal("Action")),
                                TableName = reader.GetString(reader.GetOrdinal("TableName")),
                                RecordId = reader.IsDBNull(reader.GetOrdinal("RecordId")) ? null : reader.GetString(reader.GetOrdinal("RecordId")),
                                Details = reader.IsDBNull(reader.GetOrdinal("Details")) ? null : reader.GetString(reader.GetOrdinal("Details")),
                                CreatedAt = reader.GetDateTime(reader.GetOrdinal("CreatedAt"))
                            });
                        }
                    }
                }
            }

            return logs;
        }
    }
}